using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_AUTOR : DALConnection
    {
        #region Incluir

        public void incluir(T_AUTOR obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_AUTOR ");
                SQL.Append("    (TX_NOME, FL_TIPO_AUTOR) ");
                SQL.Append(" VALUES ");
                SQL.Append("    ('" + obj.TX_NOME  + "','" + obj.FL_TIPO_AUTOR + "') ");

                DbCommand insertT_AUTOR = database.GetSqlStringCommand(SQL.ToString());

                insertT_AUTOR.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_AUTOR, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_AUTOR[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_AUTOR = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_AUTOR o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_AUTOR ");
                    SQL.Append("    (ID_AUTOR, TX_NOME, FL_TIPO_AUTOR) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (:ID_AUTOR, :TX_NOME, :FL_TIPO_AUTOR) ");

                    insertT_AUTOR[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(insertT_AUTOR[index], ":ID_AUTOR", DbType.Int64, o.ID_AUTOR);
                    database.AddInParameter(insertT_AUTOR[index], ":TX_NOME", DbType.String, o.TX_NOME);
                    database.AddInParameter(insertT_AUTOR[index], ":FL_TIPO_AUTOR", DbType.String, o.FL_TIPO_AUTOR);

                    insertT_AUTOR[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_AUTOR)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_AUTOR obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_AUTOR SET ");
                SQL.Append("    TX_NOME = '" + obj.TX_NOME + "' , ");
                SQL.Append("    FL_TIPO_AUTOR = '" + obj.FL_TIPO_AUTOR + "' ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_AUTOR = " + obj.ID_AUTOR.ToString());

                DbCommand updateT_AUTOR = database.GetSqlStringCommand(SQL.ToString());

                updateT_AUTOR.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_AUTOR, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_AUTOR[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_AUTOR = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_AUTOR o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_AUTOR SET ");
                    SQL.Append("    TX_NOME = :TX_NOME , ");
                    SQL.Append("    FL_TIPO_AUTOR = :FL_TIPO_AUTOR ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_AUTOR = :ID_AUTOR  ");

                    updateT_AUTOR[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_AUTOR[index], ":ID_AUTOR", DbType.Int64, o.ID_AUTOR);
                    database.AddInParameter(updateT_AUTOR[index], ":TX_NOME", DbType.String, o.TX_NOME);
                    database.AddInParameter(updateT_AUTOR[index], ":FL_TIPO_AUTOR", DbType.String, o.FL_TIPO_AUTOR);

                    updateT_AUTOR[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_AUTOR)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_AUTOR obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_AUTOR ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_AUTOR = " + obj.ID_AUTOR.ToString());

                DbCommand deleteT_AUTOR = database.GetSqlStringCommand(SQL.ToString());

                deleteT_AUTOR.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_AUTOR, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_AUTOR[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_AUTOR = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_AUTOR o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_AUTOR ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_AUTOR = :ID_AUTOR  ");

                    deleteT_AUTOR[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_AUTOR[index], ":ID_AUTOR", DbType.Int64, o.ID_AUTOR);

                    deleteT_AUTOR[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_AUTOR)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_AUTOR obterPorPK(long pID_AUTOR)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_AUTOR ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_AUTOR = " + pID_AUTOR);

                DbCommand consultaT_AUTOR = database.GetSqlStringCommand(SQL.ToString());
                consultaT_AUTOR.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_AUTOR, DALConnection.transaction);
                T_AUTOR obj = null;

                if (reader.Read())
                {
                    obj = new T_AUTOR();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME")))
                    {
                        obj.TX_NOME = reader["TX_NOME"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_TIPO_AUTOR")))
                    {
                        obj.FL_TIPO_AUTOR = reader["FL_TIPO_AUTOR"].ToString();
                    }
                }
                reader.Dispose();
                consultaT_AUTOR.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos
        public List<T_AUTOR> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_AUTOR ORDER BY TX_NOME");

                DbCommand consultaT_AUTOR = database.GetSqlStringCommand(SQL.ToString());
                consultaT_AUTOR.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_AUTOR, DALConnection.transaction);
                List<T_AUTOR> lista = new List<T_AUTOR>();
                T_AUTOR obj = null;

                while (reader.Read())
                {
                    obj = new T_AUTOR();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME")))
                    {
                        obj.TX_NOME = reader["TX_NOME"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_TIPO_AUTOR")))
                    {
                        obj.FL_TIPO_AUTOR = reader["FL_TIPO_AUTOR"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_AUTOR.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<T_AUTOR> consultar(string descricao, string tipo)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_AUTOR");
                SQL.Append(" WHERE TX_NOME LIKE '" + descricao + "%'");

                if (!tipo.Equals("0") && !tipo.Equals(""))
                    SQL.Append(" AND FL_TIPO_AUTOR = '" + tipo + "'");

                SQL.Append(" ORDER BY TX_NOME");
                DbCommand consultaT_AUTOR = database.GetSqlStringCommand(SQL.ToString());
                consultaT_AUTOR.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_AUTOR, DALConnection.transaction);
                List<T_AUTOR> lista = new List<T_AUTOR>();
                T_AUTOR obj = null;

                while (reader.Read())
                {
                    obj = new T_AUTOR();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR")))
                    {
                        obj.ID_AUTOR = Int64.Parse(reader["ID_AUTOR"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME")))
                    {
                        obj.TX_NOME = reader["TX_NOME"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_TIPO_AUTOR")))
                    {
                        obj.FL_TIPO_AUTOR = reader["FL_TIPO_AUTOR"].ToString();
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_AUTOR.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion        
    }
}

